# -*- coding: utf-8 -*-

'''
@project : AIGateWay
@FileName: asyn_mysql_connect
@Author  :linych 
@Time    :2020/12/3 17:45
@Desc  : 
'''


import traceback
import logging
import aiomysql
import asyncio
import time
import ailog
logobj = logging.getLogger('mysql')


class asyn_mysql_connecter():
    def __init__(self,conf):
        self.coon = None
        self.pool = None
        self.conf = conf


    async def initpool(self):
        try:
            logobj.debug("will connect mysql~")
            print(self.conf)
            __pool = await aiomysql.create_pool(
                minsize=int(self.conf.get('minsize',5)),  # 连接池最小值
                maxsize=int(self.conf.get('maxsize',10)),  # 连接池最大值
                host=self.conf['host'],
                port=int(self.conf['port']),
                user=self.conf['user'],
                password=self.conf['password'],
                db=self.conf['db'],
                autocommit=True,  # 自动提交模式
            )
            self.pool = __pool
        except Exception as e:
            # ailog.error('connect error.', exc_info=True)
            raise e

    async def getCurosr(self):

        if self.pool is None:

            await self.initpool()

        conn = await self.pool.acquire()
        # 返回字典格式
        cur = await conn.cursor(aiomysql.DictCursor)
        return conn, cur

    async def query(self, query, param=None):
        """
        查询操作
        :param query: sql语句
        :param param: 参数
        :return:
        """
        conn, cur = await self.getCurosr()
        try:
            await cur.execute(query, param)
            return await cur.fetchall()
        except Exception as e:
            # ailog.error(traceback.format_exc())
            raise e
        finally:
            if cur:
                await cur.close()
            # 释放掉conn,将连接放回到连接池中
            await self.pool.release(conn)

    async def execute(self, query, param=None):
        """
        增删改 操作
        :param query: sql语句
        :param param: 参数
        :return:
        """
        conn, cur = await self.getCurosr()
        try:
            await cur.execute(query, param)
            await conn.commit()

            if cur.rowcount == 0:
                return False
            else:
                return True
        except Exception as e:
            # ailog.error(e)
            raise e
        finally:
            if cur:
                await cur.close()
            # 释放掉conn,将连接放回到连接池中
            await self.pool.release(conn)

    async def check_connect(self):

        conn, cur = await self.getCurosr()

        if conn is None:
            print('#MySQL 未连接。')
            return False

        flag = True
        try:
            # 判断是否为长连接2006异常
            await conn.ping()
        except:
            print('#MySQL 链接异常。')

            flag = False

        return flag

        # 检查连接,若连接失败重连
    async def check_reconnect(self, reset=True):
        for _ in range(6):
            if await self.check_connect():
                # if reset:
                #     # 清空缓存，防止脏读
                #     self.reset_connect_cache()
                return True
            else:
                print('MySQL reconnect ...')
                self.initpool()
                if await self.check_connect():
                    return True

            time.sleep(5)

            return False



            #
#
#
# async def getAmysqlobj():
#     mysqlobj = Pmysql()
#     pool = await mysqlobj.initpool()
#     mysqlobj.pool = pool
#     return mysqlobj
#
#
# async def test_select():
#     mysqlobj = await getAmysqlobj()
#     # UPDATE `youku`.`person` SET `psName` = '张三丰' WHERE (`id` = '3');
#     exeRtn = await mysqlobj.query("select * from users")
#     # print("查询结果",exeRtn)
#     return exeRtn
#
#
# async def test_update():
#     mysqlobj = await getAmysqlobj()
#     # UPDATE `youku`.`person` SET `psName` = '张三丰' WHERE (`id` = '3');
#     exeRtn = await mysqlobj.execute("update users set username='xiao1' where id='1'")
#     # print("exeRtn", exeRtn, type(exeRtn))
#     if exeRtn:
#         # print('操作成功')
#         return '操作成功'
#     else:
#         # print('操作失败')
#         return '操作失败'
#
#
# async def main():  # 调用方
#     tasks = [test_select(), test_update()]  # 把所有任务添加到task中
#     done, pending = await asyncio.wait(tasks)  # 子生成器
#     for r in done:  # done和pending都是一个任务，所以返回结果需要逐个调用result()
#         # print('协程无序返回值：'+r.result())
#         print(r.result())
#
#
# if __name__ == '__main__':
#     start = time.time()
#     loop = asyncio.get_event_loop()  # 创建一个事件循环对象loop
#     try:
#         loop.run_until_complete(main())  # 完成事件循环，直到最后一个任务结束
#     finally:
#         loop.close()  # 结束事件循环
#     print('所有IO任务总耗时%.5f秒' % float(time.time() - start))
